#coding=utf-8

import tushare as ts
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import pandas as pd

path ="D:\Project\P_data\python"
con_str='mysql://root:root@10.200.15.120/test?charset=utf8'

def get_date():
    df_m_his = ts.get_tick_data('600518',date='2016-02-02')   ##获取历史分时数据
    df_m = ts.get_realtime_quotes('600518')     ##获取当前时刻数据
    df_d = ts.get_hist_data('600518')   #获取天数据
    print df_d.head(10)

def get_sh_index():
    df = ts.get_index()
    print df.loc[:,['open','close']][0:1]

def chart_his():
    df = ts.get_hist_data('sh',start='2016-01-01',end='2016-02-03')
    # print type(df)    #查看数据类型
    # print df.columns  #显示df里面的列名
    # print df.index
    # print df['close'].head(5)
    # print df['close'].tail(5)
    # print df['close'][0:3]  ##选择行，获取前3行
    # print df.close
    print df.loc[:,['open','close']].head(3)    ##选择多列
    df_ac = df.sort_index() #顺序排列
    # print df_ac.close.mean(axis=0) #求均值，其中axis=0，表示去掉了index
    with pd.plot_params.use('x_compat', True):
        df_ac.close.plot(color='r') #颜色有g,y,r,b
        df_ac.ma5.plot(color='g')
        df_ac.ma10.plot(color='b')
    plt.legend(loc='best')
    plt.show()

def test_conn_mssql():
    engine = create_engine(con_str)
    connection = engine.connect()
    result = connection.execute("SELECT id as username FROM user_role")
    for row in result:
        print("username:", row['username'])
    connection.close()
    print 'ok'

def test_insert_col(code):
    df_d = ts.get_hist_data(code)
    df_d.insert(0,'code',code)
    print df_d.head(1)
    print 'ok'

def get_stock_list_csv():
    df = ts.get_stock_basics()
    fileName = path + "\\stock_basic_list.csv"
    df.to_csv(fileName)
    print 'download csv finish'

def get_stock_list_db():
    df = ts.get_stock_basics()
    db_connect_str=con_str
    engine = create_engine(db_connect_str,echo=True)
    del_str='delete from tick_list'
    engine.execute(del_str)
    df.to_sql('tick_list',engine,if_exists='append')
    print 'ok'

def get_stock_his(code):
    df_d = ts.get_hist_data(code)
    if df_d is not None:
        df_d.insert(0,'code',code)
        # print df_d.head(1)
        db_connect_str=con_str
        engine = create_engine(db_connect_str,echo=True)
        del_str='delete from tick_data where code =%s'%code
        engine.execute(del_str)
        df_d.to_sql('tick_data',engine,if_exists='append')
        print 'ok'

def stock_is_exist(code):
    db_connect_str=con_str
    engine = create_engine(db_connect_str,echo=True)
    str='select code from tmp_tick_data_02_code where code =%s limit 1'%code
    rs=len(engine.execute(str).fetchall())
    if rs==1:
        return False
    else:
        return True

def get_stock_loop():
    db_connect_str=con_str
    engine = create_engine(db_connect_str,echo=True)
    tick_list_str='select code from tick_list'
    tick_list_rs=engine.execute(tick_list_str).fetchall()
    print type(tick_list_rs)
    print len(tick_list_rs)
    print type(tick_list_rs[0])
    print tick_list_rs[0]['code']
    for i in range(len(tick_list_rs)):       ##range(len(tick_list_rs))  [0,1]
        print tick_list_rs[i]['code']
        if stock_is_exist(tick_list_rs[i]['code']):
            get_stock_his(tick_list_rs[i]['code'])

def test_nonetype(code):
    df_d = ts.get_hist_data(code)
    if df_d is not None:
        print 'none'
    # print df_d
    # df_d.insert(0,'code',code)
    # print df_d.head(1)

if __name__ == '__main__':
    get_stock_list_db()
    # get_stock_his('300516')
    # print stock_is_exist('000616')
    # test_nonetype('000616')   ##300516 000616
    # get_stock_loop()

